﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using PACS.Data;
using WoSoft.PACS.Data.Models;
using WoSoft.PACS.Utility;

namespace WoSoft.PACS.Data.DataSourceData
{
    /// <summary>
    /// 数据源保存
    /// </summary>
    public class DataSourceService : Db
    {
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InsertData(DataSourceModel model)
        {
            return this.SqlDatabase.Insert("数据源配置")
                .Column("业务类型", model.YwType)
                .Column("名称", model.Name)
                .Column("数据库类型", model.DatabaseType)
                .Column("IP地址", model.ServerIP)
                .Column("实例名", model.Instance)
                .Column("数据库名称", model.DatabaseName)
                .Column("用户名", model.UserName)
                .Column("登录密码", model.Password)
                .Execute();
        }

        /// <summary>
        /// 获取所有数据源配置
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllData()
        {
            var sql = "SELECT  ID , 业务类型 ,名称 , CASE (数据库类型)  WHEN 1 THEN 'Oracle数据库'" +
                    "WHEN 2 THEN 'SqlServer数据库'  END  AS 数据库类型,   IP地址, 实例名 FROM  dbo.数据源配置";
            return this.SqlDatabase.Sql(sql)
                .QuerySingle<DataTable>();
        }

        /// <summary>
        /// 根据ID获取模型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSourceModel GetModelByID(int _id)
        {
            var sql = "select * from 数据源配置 where ID = @id";
            var dt = this.SqlDatabase.Sql(sql)
                .Parameter("ID", _id)
               .QuerySingle<DataTable>();
            if (dt != null)
            {
                var model = new DataSourceModel()
                {
                    ID = Convert.ToInt32(dt.GetColumnValue("ID")),
                    YwType = dt.GetColumnValue("业务类型").ToString(),
                    Name = dt.GetColumnValue("名称").ToString(),
                    DatabaseName = dt.GetColumnValue("数据库名称").ToString(),
                    DatabaseType = Convert.ToInt32(dt.GetColumnValue("数据库类型")),
                    Instance = dt.GetColumnValue("实例名").ToString(),
                    ServerIP = dt.GetColumnValue("IP地址").ToString(),
                    UserName = dt.GetColumnValue("用户名").ToString(),
                    Password = dt.GetColumnValue("登录密码").ToString(),
                    SqlText = dt.GetColumnValue("查询语句").ToString()
                };

                return model;
            }

            return null;
        }

        /// <summary>
        /// 根据业务类型获取模型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSourceModel GetModelByID(string _ywType)
        {
            var sql = "select * from 数据源配置 where 业务类型 = @ywType";
            var dt = this.SqlDatabase.Sql(sql)
                .Parameter("ywType", _ywType)
               .QuerySingle<DataTable>();
            if (dt != null)
            {
                var model = new DataSourceModel()
                {
                    ID = Convert.ToInt32(dt.GetColumnValue("ID")),
                    YwType = dt.GetColumnValue("业务类型").ToString(),
                    Name = dt.GetColumnValue("名称").ToString(),
                    DatabaseName = dt.GetColumnValue("数据库名称").ToString(),
                    DatabaseType = Convert.ToInt32(dt.GetColumnValue("数据库类型")),
                    Instance = dt.GetColumnValue("实例名").ToString(),
                    ServerIP = dt.GetColumnValue("IP地址").ToString(),
                    UserName = dt.GetColumnValue("用户名").ToString(),
                    Password = dt.GetColumnValue("登录密码").ToString(),
                    SqlText = dt.GetColumnValue("查询语句").ToString()
                };

                return model;
            }

            return null;
        }

        /// <summary>
        /// 更新配置数据源
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateData(DataSourceModel model)
        {
            return this.SqlDatabase.Update("数据源配置")
               .Column("业务类型", model.YwType)
               .Column("名称", model.Name)
               .Column("数据库类型", model.DatabaseType)
               .Column("IP地址", model.ServerIP)
               .Column("实例名", model.Instance)
               .Column("数据库名称", model.DatabaseName)
               .Column("用户名", model.UserName)
               .Column("登录密码", model.Password)
               .Where("ID", model.ID)
               .Execute();
        }

        /// <summary>
        /// 删除配置数据源
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(string _ID)
        {
            return this.SqlDatabase.Delete("数据源配置")
               .Where("ID", _ID)
               .Execute();
        }

        /// <summary>
        /// 保存SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlType">脚本类型</param>
        /// <returns></returns>
        public int SaveDataSql(string sql, int dataSourceId, string sqlType)
        {
            return this.SqlDatabase.Update("数据源配置")
                 .Column("查询语句", sql)
                 .Where("ID", dataSourceId)
                 .Where("业务类型", sqlType)
                 .Execute();
        }
    }
}
